package com.sql;

import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;

/**
 *
 * @author Khaled Lela
 */
public class CreateDB {
    /*
     * Check DataBase Exist Or Not
     * If Database doesn't exist call Constractor CreateDB(boolean) which creat it .
     */

    public CreateDB() {
        try {
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
            DriverManager.getConnection("jdbc:derby:BreadDB;create=false");
        } catch (Exception e) {
            db = new CreateDB(false);
        } finally {
            db = null;
        }
    }

    /*
     * Constrator that create database
     */
    public CreateDB(boolean isExist) {
        if (!isExist) {
            try {
                con = DriverManager.getConnection("jdbc:derby:BreadDB;create=true");
                statement = con.createStatement();
            } catch (Exception e) {
            }
            loginTb();
            distributor();
            people();
        }
    }

//Create Login table && insert default passWord
    private void loginTb() {
        String loginSql = "create table login (id integer generated always as identity primary key, "
                + "username varchar(100) unique, password varchar(100))";
        String defaultPassWord = "insert into login (username, password) values ('admin', 'admin')";
        executeSql(loginSql);
        executeSql(defaultPassWord);
    }

    /*
     * Methods which create data base tables
     */
    private void people() {
        String Sql = "create table People ("
                + " PersonID SMALLINT primary key , "
                + " PersonName varchar(100) UNIQUE, "
                + " Person_Num Integer ,"
                + " DistributorID SMALLINT  CONSTRAINT Distributor_foreign_key "
                + " REFERENCES Distributor ON DELETE CASCADE ON UPDATE RESTRICT,"
                + " Category varchar(1) )";
        executeSql(Sql);
    }

    private void distributor() {
        String Sql = "create table Distributor ("
                + " DistributorID SMALLINT generated always as identity primary key , "
                + " Name varchar(100) UNIQUE ,"
                + " Region varchar(100) , "
                + " Phone INT , "
                + " Quantity SMALLINT)";
        executeSql(Sql);
    }

    public void payer() {
        String Sql = "create table Payer("
                + " PayID SMALLINT generated always as identity primary key , "
                + " PersonID SMALLINT CONSTRAINT payer_foreign_key "
                + " REFERENCES People ON DELETE CASCADE ON UPDATE RESTRICT,"
                + " PayYear SMALLINT , "
                + " PayMonth SMALLINT ,"
                + " PayDay SMALLINT ,"
                + " UNIQUE ( PersonID,PayYear,PayMonth ) )";
        executeSql(Sql);
    }

    private void executeSql(String sql) {
        try {
            statement.execute(sql);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    private Connection con;
    private Statement statement;
    private CreateDB db;
}
